package com.dataseek.iot.toolikit.common.util;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.dataseek.iot.toolikit.common.consts.SysConsts;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.text.MessageFormat;
import java.util.*;
import java.util.Date;

/**
 * 数据库工具类
 */
@Slf4j
public class DBUtils {
    public static final String DB_TYPE_POSTGRESQL = "postgresql";
    public static final String DB_TYPE_MYSQL = "mysql";

    /**
     * @param dbType   数据库类型
     * @param host     数据库主机地址
     * @param port     端口号
     * @param database 数据库名
     * @param userName 用户名
     * @param password 密码
     * @return
     */
    public static Connection getConnection(
            String dbType,
            String host,
            Integer port,
            String database,
            String userName,
            String password
    ) {
        Connection connection = null;
        String jdbcUrl = null;
        try {
            String driverName = null;
            //如果数据库类型为
            if (DB_TYPE_POSTGRESQL.equalsIgnoreCase(dbType)) {
                driverName = "org.postgresql.Driver";
                jdbcUrl = "jdbc:postgresql://"
                        + host
                        + ":"
                        + port
                        + "/"
                        + database
                        + "?serverTimezone=GMT%2b8&useUnicode=true&characterEncoding=UTF-8&connectTimeout=5000&socketTimeout=60000";

                ;
                log.info("jdbcUrl:" + jdbcUrl);
            } else if (DB_TYPE_MYSQL.equalsIgnoreCase(dbType)) {
                driverName = "com.mysql.cj.jdbc.Driver";
                jdbcUrl = "jdbc:mysql://"
                        + host
                        + ":"
                        + port
                        + "/"
                        + database
                        + "?serverTimezone=GMT%2b8&useUnicode=true&characterEncoding=UTF-8&connectTimeout=5000&socketTimeout=60000";
            }
            Class.forName(driverName).newInstance();
            connection = DriverManager.getConnection(jdbcUrl, userName, password);
        } catch (Exception e) {

            log.error(e.getMessage());
        }
        return connection;
    }

    //单语句执行
    public static Integer execute(Connection connection, String sql) {
        Statement pstmt =null;
        Integer code = 0;
        try {
            pstmt = connection.createStatement();
            pstmt.execute(sql);
        } catch (Exception e) {
            code = -1;
            log.error(e.getMessage());
        } finally {
            close(pstmt, connection);
        }
        return code;
    }

    /**
     * @method insert(Object tenant) 往表中插入数据
     * @return int 成功插入数据条数
     */
    public static int insertTenant(Connection connection, Map<String, Object> tenant) {
        Integer code = 0;
        String sql = "insert into tenant.sys_tenant (tenant_id,company_name,company_code,company_address,telephone,database_name,super_admin_name,super_admin_password,status,create_by,create_time,update_by,update_time,db_host,db_type,db_port,schema_name,driver_name,tenant_name,tenant_password) " +
                "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement pstmt = null;
        try {
            pstmt = (PreparedStatement) connection.prepareStatement(sql);
            pstmt.setInt(1, Integer.parseInt(tenant.get("tenant_id").toString()));
            pstmt.setString(2,  String.valueOf(tenant.get("company_name")));
            pstmt.setString(3,  String.valueOf(tenant.get("company_code")));
            pstmt.setString(4,  String.valueOf(tenant.get("company_address")));
            pstmt.setString(5,  String.valueOf(tenant.get("telephone")));
            pstmt.setString(6,  String.valueOf(tenant.get("database_name")));
            pstmt.setString(7,  String.valueOf(tenant.get("super_admin_name")));
            pstmt.setString(8,  String.valueOf(tenant.get("super_admin_password")));
            pstmt.setString(9,  String.valueOf(tenant.get("status")));
            pstmt.setString(10, String.valueOf(tenant.get("create_by")));
            pstmt.setTimestamp(11, DateUtils.stringToTimestamp(String.valueOf(tenant.get("create_time"))));
            pstmt.setString(12, String.valueOf(tenant.get("update_by")));
            pstmt.setTimestamp(13, DateUtils.stringToTimestamp(String.valueOf(tenant.get("update_time"))));
            pstmt.setString(14, String.valueOf(tenant.get("db_host")));
            pstmt.setString(15, String.valueOf(tenant.get("db_type")));
            pstmt.setInt(16, Integer.parseInt(tenant.get("db_port").toString()));
            pstmt.setString(17, String.valueOf(tenant.get("schema_name")));
            pstmt.setString(18, String.valueOf(tenant.get("driver_name")));
            pstmt.setString(19, String.valueOf(tenant.get("tenant_name")));
            pstmt.setString(20, String.valueOf(tenant.get("tenant_password")));
            code = pstmt.executeUpdate();
        } catch (Exception e) {
            code = -1;
            log.error(e.getMessage());
        } finally {
            close(pstmt, connection);
        }
        return code;
    }

    /**
     * @method insert(Object tenant) 往表中插入数据
     * @return int 成功插入数据条数
     */
    public static int insertMenu(Connection connection, List<Map<String, Object>> menuList,String databaseName) {
        Integer code = 0;
        String sql = "insert into tenant.sys_menu (tenant_code,menu_id,menu_name,parent_id,order_num,path,icon,perms,menu_type,status,create_by,create_time,update_by,update_time,is_uniq) " +
                "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        String sqlRoleMenu = "insert into tenant.sys_role_menu (tenant_code,role_id,menu_id,rel_id) " +
                "values (?,?,?,?)";
        PreparedStatement pstmt = null;
        PreparedStatement pstmt2 = null;
        try {
            for (int i = 0; i < menuList.size(); i++) {
                Map<String, Object> menu = menuList.get(i);
                pstmt = (PreparedStatement) connection.prepareStatement(sql);
                pstmt2 = (PreparedStatement) connection.prepareStatement(sqlRoleMenu);
                pstmt.setString(1, databaseName);
                Integer menuId=Integer.parseInt(String.valueOf(menu.get("menu_id")));
                pstmt.setInt(2, menuId);
                pstmt.setString(3, String.valueOf(menu.get("menu_name")));
                pstmt.setString(4, String.valueOf(menu.get("parent_id")));
                pstmt.setInt(5, Integer.parseInt(String.valueOf(menu.get("order_num"))));
                pstmt.setString(6, String.valueOf(menu.get("path")));
                pstmt.setString(7, String.valueOf(menu.get("icon")));
                pstmt.setString(8, String.valueOf(menu.get("perms")));
                pstmt.setString(9, String.valueOf(menu.get("menu_type")));
                pstmt.setString(10, String.valueOf(menu.get("status")));
                pstmt.setString(11, String.valueOf(menu.get("create_by")));
                pstmt.setTimestamp(12, DateUtils.stringToTimestamp(String.valueOf(menu.get("create_time"))));
                pstmt.setString(13, String.valueOf(menu.get("update_by")));
                pstmt.setTimestamp(14, DateUtils.stringToTimestamp(String.valueOf(menu.get("update_time"))));
                pstmt.setString(15, String.valueOf(menu.get("is_uniq")));
                pstmt2.setString(1, databaseName);
                pstmt2.setInt(2,99999);
                pstmt2.setInt(3,menuId);
                pstmt2.setLong(4,SnowflakeIdWorker.nextId());
                code = pstmt.executeUpdate();
                pstmt2.executeUpdate();
            }
        } catch (Exception e) {
            code = -1;
            log.error(e.getMessage());
        } finally {
            close(pstmt, connection);
            close(pstmt2, connection);
        }
        return code;
    }

    //执行创建语句
    public static Integer create(Connection connection, String databaseName) {
        Statement pstmt =null;
        Integer code = 0;
        try {
            pstmt = connection.createStatement();
            String sql = "CREATE DATABASE {0};";
            log.info("sql:" + sql);
            sql = MessageFormat.format(sql, databaseName);
            String sql2 = "CREATE SCHEMA {0}.tenant;";
            sql2 = MessageFormat.format(sql2, databaseName);
            log.info("sql2:" + sql2);
            pstmt.addBatch(sql);
            pstmt.addBatch(sql2);
            pstmt.executeBatch();
        } catch (Exception e) {
            code = -1;
            log.error(e.getMessage());
        } finally {
            close(pstmt, connection);
        }
        return code;
    }

    //执行批量创建表语句
    public static Integer batchTableCreate(Connection connection, String databaseName) {
        Integer code = 0;
        Statement pstmt = null;
        JSONArray jsonArray = new JSONArray();
        try {
            pstmt = connection.createStatement();
            String sql_tenant = "CREATE TABLE tenant.sys_tenant (tenant_id serial NOT NULL,company_name varchar(30) NOT NULL,company_code varchar(30) NOT NULL,company_address varchar(30) NOT NULL,telephone varchar(20) NOT NULL,database_name varchar(20) NOT NULL,super_admin_name varchar(50) NOT NULL,super_admin_password varchar(50) NOT NULL,status bpchar(1) NULL DEFAULT '1'::bpchar,create_by varchar(64) NULL DEFAULT ''::character varying,create_time timestamp NULL DEFAULT CURRENT_TIMESTAMP,update_by varchar(64) NULL DEFAULT ''::character varying,update_time timestamp NULL DEFAULT CURRENT_TIMESTAMP,db_host varchar(100) NULL,db_type varchar(50) NULL,db_port int4 NULL,schema_name varchar(100) NULL,driver_name varchar(100) NULL,tenant_name varchar(100) NULL,tenant_password varchar(100) NULL,CONSTRAINT sys_tenant_pkey PRIMARY KEY (tenant_id));";
            String sql_user = "create table tenant.sys_user ( tenant_code varchar(20) , user_id serial PRIMARY KEY , user_name varchar(100) not null , user_password varchar(100) not null, telephone varchar(20) , email varchar(50) , status char(1) default '1', create_by varchar(20) , create_time TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP , update_by varchar(20) ,update_time TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP ,user_type varchar(50) NULL) ; ";
            String sql_dept = "create table tenant.sys_dept ( tenant_code varchar(20) , dept_id serial PRIMARY KEY , dept_name varchar(100) , status varchar(20) , create_by varchar(20) , create_time TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP , update_by varchar(20) , update_time TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP ) ; ";
            String sql_role = "create table tenant.sys_role ( tenant_code varchar(20) , role_id serial PRIMARY KEY , role_name varchar(100) , role_key varchar(100) , role_set varchar(20) , remark varchar(250) , status varchar(20) , create_by varchar(20) , create_time TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP , update_by varchar(20) , update_time TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP ) ; ";
            String sql_menu = "create table tenant.sys_menu ( tenant_code varchar(20) , menu_id serial PRIMARY KEY , menu_name varchar(100) , parent_id varchar(100) , order_num bigint , path varchar(250), icon varchar(250)  , perms varchar(250) , menu_type varchar(20) , status varchar(20) , create_by varchar(20) , create_time TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP , update_by varchar(20) , update_time TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP ,is_uniq bpchar(1)) ; ";
            String sql_user_role = "create table tenant.sys_user_role ( tenant_code varchar(20) , user_id bigint , role_id bigint ,rel_id int8 NOT NULL  PRIMARY KEY); ";
            String sql_user_dept = "create table tenant.sys_user_dept ( tenant_code varchar(20) , user_id bigint , dept_id bigint ,rel_id int8 NOT NULL  PRIMARY KEY); ";
            String sql_role_menu = "create table tenant.sys_role_menu ( tenant_code varchar(20) , role_id bigint , menu_id bigint ,rel_id int8 NOT NULL  PRIMARY KEY); ";
            pstmt.addBatch(sql_tenant);
            pstmt.addBatch(sql_user);
            pstmt.addBatch(sql_dept);
            pstmt.addBatch(sql_role);
            pstmt.addBatch(sql_menu);
            pstmt.addBatch(sql_user_role);
            pstmt.addBatch(sql_user_dept);
            pstmt.addBatch(sql_role_menu);
            pstmt.executeBatch();
            //connection.commit();
        } catch (Exception e) {
            code = -1;
            log.error(e.getMessage());
        } finally {
            close(pstmt, connection);
        }
        return code;
    }



    //执行批量插入表语句
    public static Integer batchTableInsert(Connection connection, String databaseName, String tenantName, String tenantPw) {
        Integer code = 0;
        Statement pstmt = null;
        JSONArray jsonArray = new JSONArray();
        try {
            pstmt = connection.createStatement();
            String user_insert = "insert into tenant.sys_user (tenant_code,user_id,user_name,user_password,status,create_by,create_time,update_by,update_time,user_type) values ({0},{2},{3},{4},{5},{0},{1},{0},{1},{6})";
            user_insert = MessageFormat.format(user_insert, "'" + databaseName + "'" , "'" + new Date() + "'" , "'" + "99999" + "'", "'" + tenantName + "'", "'" + tenantPw + "'", "'" + "1" + "'", "'" + SysConsts.USER_TYPE_TENANT_ADMIN + "'");
            String role_insert = "insert into tenant.sys_role (tenant_code,role_id,role_name,role_set,status,create_by,create_time,update_by,update_time) values ({0},{2},{3},{4},{5},{0},{1},{0},{1})";
            role_insert = MessageFormat.format(role_insert, "'" + databaseName + "'" , "'" + new Date() + "'" , "'" + "99999" + "'", "'" + "租户管理员" + "'", "'" + "0" + "'", "'" + "1" + "'");
            String user_role_insert= "insert into tenant.sys_user_role (tenant_code,user_id,role_id,rel_id) values ({0},{1},{2},{3})";
            user_role_insert = MessageFormat.format(user_role_insert, "'" + databaseName + "'" , "'" + "99999" + "'","'" + "99999" + "'","'" + SnowflakeIdWorker.nextId() + "'");
            pstmt.addBatch(user_insert);
            pstmt.addBatch(role_insert);
            pstmt.addBatch(user_role_insert);
            pstmt.executeBatch();
            //connection.commit();
        } catch (Exception e) {
            code = -1;
            log.error(e.getMessage());
        } finally {
            close(pstmt, connection);
        }
        return code;
    }

    public static ResultSet query(Connection conn, String sql) {
        PreparedStatement pStatement = null;
        ResultSet rs = null;
        try {
            //准备PreparedStatement
            pStatement = conn.prepareStatement(sql);
            //执行查询
            rs = pStatement.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }


    //释放与关闭资源
    public static void close(Statement statement, Connection connection) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                log.error(e.getMessage());
            }
        }

        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                log.error(e.getMessage());
            }
        }
    }

    public static List<Map<String,Object>> formatDatabaseData(ResultSet resultSet){
        List<Map<String,Object>> resultList = new ArrayList<>();
        List<String> columnNames= new ArrayList<>();
        // 获取列名
        try{
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            for (int i = 1; i < resultSetMetaData.getColumnCount() + 1; i++) {
                System.out.println(resultSetMetaData.getColumnName(i));
                columnNames.add(resultSetMetaData.getColumnName(i));
            }
            // 根据列名获取每一行的数据
            while (resultSet.next()) {
                Map<String,Object> data = new HashMap<>();
                for (String columnName :columnNames){
                    data.put(columnName,resultSet.getString(columnName));
                }
                resultList.add(data);
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        return resultList;
    }




}
